Query Used in report: ========================================================================================================================= SELECT Z.SENT_IND AS IS_SENT, COALESCE(X.EMAILS, 0) AS EMAILS, COALESCE(X.PERCENT, 0) PERCENT FROM ( SELECT CASE WHEN SENT_IND <= 1 THEN 1 WHEN SENT_IND <= 5 THEN 2 WHEN SENT_IND <= 10 THEN 3 WHEN SENT_IND <= 15 THEN 4 WHEN SENT_IND <= 20 THEN 5 WHEN SENT_IND <= 30 THEN 6 WHEN SENT_IND <= 50 THEN 7 WHEN SENT_IND <= 75 THEN 8 WHEN SENT_IND <= 100 THEN 9 ELSE 10 END AS SEND_RANK, COUNT(DISTINCT TARGET_ID) AS EMAILS, (COUNT(DISTINCT TARGET_ID)+0.0) / ( SELECT COUNT(DISTINCT TARGET_ID) FROM S_V_F_PROMOTION_HISTORY_EMAIL PH INNER JOIN S_V_D_CAMPAIGN_HIERARCHY CH ON PH.TOUCHPOINT_EXECUTION_ID = CH.TOUCHPOINT_EXECUTION_ID WHERE 1=1 AND SEND_DT >= '2014-03-13' AND SEND_DT <= '2015-03-14' -- AND ($X{IN,CH.CAMPAIGN_INITIATIVE,param_CAMPAIGN_INITIATIVE_2} OR $X{IN,'--ALL INITIATIVES--',param_CAMPAIGN_INITIATIVE_2}) ) AS PERCENT FROM ( SELECT TARGET_ID, COUNT(PROMO_HIST_ID) AS SENT_IND FROM S_V_F_PROMOTION_HISTORY_EMAIL PH INNER JOIN S_V_D_CAMPAIGN_HIERARCHY CH ON PH.TOUCHPOINT_EXECUTION_ID = CH.TOUCHPOINT_EXECUTION_ID WHERE 1=1 AND SEND_DT >= '2014-01-01' AND SEND_DT <= '2015-03-14' AND ($X{IN, CH.CAMPAIGN_INITIATIVE, param_CAMPAIGN_INITIATIVE_2} OR $X{IN, '--ALL INITIATIVES--', param_CAMPAIGN_INITIATIVE_2}) GROUP BY TARGET_ID ) A GROUP BY SEND_RANK ) X RIGHT OUTER JOIN ( SELECT 1 AS SEND_RANK, '1' AS SENT_IND UNION SELECT 2, '2-5' UNION SELECT 3, '6-10' UNION SELECT 4, '11-15' UNION SELECT 5, '16-20' UNION SELECT 6, '21-30' UNION SELECT 7, '31-50' UNION SELECT 8, '51-75' UNION SELECT 9, '76-100' UNION SELECT 10, '101 OR MORE' ) Z ON Z.SEND_RANK = X.SEND_RANK ORDER BY Z.SEND_RANK ASC ; ========================================================================================================================= Views Used in the campaigns: S_V_F_PROMOTION_HISTORY_EMAIL S_V_D_CAMPAIGN_HIERARCHY ========================================================================================================================= S_V_F_PROMOTION_HISTORY_EMAIL: WITH valid_executions AS ( SELECT ste.touchpoint_execution_id FROM s_v_d_successful_touchpoint_executions ste JOIN s_d_touchpoint_execution tpe ON ste.touchpoint_execution_id = tpe.touchpoint_execution_id JOIN s_d_touchpoint tp ON tpe.touchpoint_id = tp.touchpoint_id AND tp.channel_type_id = 1 ) SELECT base.promo_hist_id, base.audience_member_id, base.target_id, base.touchpoint_execution_id, base.contact_group_id, base.content_version_execution_id, base.sent_ind, CASE WHEN email.sbounce_ind IS NOT NULL THEN email.sbounce_ind::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS sbounce_ind, CASE WHEN email.hbounce_ind IS NOT NULL THEN email.hbounce_ind::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS hbounce_ind, CASE WHEN email.opened_ind IS NOT NULL THEN email.opened_ind::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS opened_ind, CASE WHEN email.clicked_ind IS NOT NULL THEN email.clicked_ind::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS clicked_ind, CASE WHEN email.unsubscribe_ind IS NOT NULL THEN email.unsubscribe_ind::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS unsubscribe_ind, CASE WHEN email.unsubscribe_spam_ind IS NOT NULL THEN email.unsubscribe_spam_ind::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS unsubscribe_spam_ind, CASE WHEN email.gross_open_cnt IS NOT NULL THEN email.gross_open_cnt::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS gross_open_cnt, CASE WHEN email.distinct_click_cnt IS NOT NULL THEN email.distinct_click_cnt::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS distinct_click_cnt, CASE WHEN email.gross_click_cnt IS NOT NULL THEN email.gross_click_cnt::integer WHEN 0 IS NOT NULL THEN 0 ELSE NULL::integer END AS gross_click_cnt, base.send_dt, email.first_click_dt, email.first_open_dt, email.unsubscribe_dt, email.unsubscribe_spam_dt, email.bounce_dt, CASE WHEN email.creation_dt IS NOT NULL THEN email.creation_dt WHEN base.creation_dt IS NOT NULL THEN base.creation_dt ELSE NULL::timestamp without time zone END AS creation_dt, CASE WHEN email.modified_dt IS NOT NULL THEN email.modified_dt WHEN base.modified_dt IS NOT NULL THEN base.modified_dt ELSE NULL::timestamp without time zone END AS modified_dt FROM s_f_promotion_history base LEFT JOIN s_f_promotion_history_email email ON base.promo_hist_id = email.promo_hist_id AND base.audience_member_id = email.audience_member_id JOIN valid_executions ON base.touchpoint_execution_id = valid_executions.touchpoint_execution_id; S_V_D_CAMPAIGN_HIERARCHY : WITH valid_executions AS ( SELECT s_f_touchpoint_execution_status_history.touchpoint_execution_id FROM s_f_touchpoint_execution_status_history JOIN ( SELECT s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, max(s_f_touchpoint_execution_status_history_1.creation_dt) AS last_status_change FROM s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1 GROUP BY s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) tmp ON s_f_touchpoint_execut ion_status_history.touchpoint_execution_id = tmp.touchpoint_execution_id AND s_f_touchpoint_execution_status_history.creat ion_dt = tmp.last_status_change AND (s_f_touchpoint_execution_status_history.touchpoint_execution_status_type_id = ANY (AR RAY[3, 4])) ) SELECT camp.campaign_id, camp.campaign_name, camp.initiative AS campaign_initiative, camp.objective AS campaign_objective, camp.category_id AS campaign_category_id, "CATEGORY".category_name AS campaign_category_name, camp_exec.campaign_execution_id, camp_exec.campaign_execution_name, camp_exec.group_id, grup.group_name, camp_exec.start_dt AS campaign_execution_start_dt, wave.wave_id, wave.wave_name, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt AS wave_execution_start_dt, tp.touchpoint_id, tp.touchpoint_name, tp_exec.touchpoint_execution_id, tp_exec.start_dt AS touchpoint_execution_start_dt, channel.channel_type_id, channel.channel_type_name, tp_exec.message_type_id, message_type.message_type_name, tp_exec.content_id, content.content_name FROM s_d_touchpoint_execution tp_exec JOIN s_d_wave_execution wave_exec ON tp_exec.wave_execution_id = wave_exec.wave_execution_id JOIN s_d_campaign_execution camp_exec ON wave_exec.campaign_execution_id = camp_exec.campaign_execution_id JOIN s_d_touchpoint tp ON tp_exec.touchpoint_id = tp.touchpoint_id JOIN s_d_wave wave ON wave_exec.wave_id = wave.wave_id AND tp.wave_id = wave.wave_id JOIN s_d_campaign camp ON camp_exec.campaign_id = camp.campaign_id AND wave.campaign_id = camp.campaign_id LEFT JOIN s_d_content content ON tp_exec.content_id = content.content_id LEFT JOIN s_d_message_type message_type ON tp_exec.message_type_id = message_type.message_type_id LEFT JOIN s_d_group grup ON camp_exec.group_id = grup.group_id JOIN s_d_channel_type channel ON tp.channel_type_id = channel.channel_type_id LEFT JOIN s_d_category "CATEGORY" ON camp.category_id = "CATEGORY".category_id JOIN valid_executions ON tp_exec.touchpoint_execution_id = valid_executions.touchpoint_execution_id; ========================================================================================================================= Tables used in views along with the counts: These counts are for the testing server. S_V_F_PROMOTION_HISTORY_EMAIL: s_v_d_successful_touchpoint_executions (This is again a view). See definition below. s_d_touchpoint_execution - 72956 s_d_touchpoint - 10972 s_f_promotion_history - 10000000 s_f_promotion_history_email - 46971 S_V_D_CAMPAIGN_HIERARCHY: s_f_touchpoint_execution_status_history -- 291104 s_d_touchpoint_execution -- 72956 s_d_wave_execution -- 46247 s_d_campaign_execution -- 46244 s_d_touchpoint -- 10792 s_d_wave -- 9455 s_d_campaign -- 9433 s_d_content - 3180 s_d_message_type - 3 s_d_group - 7 s_d_channel_type - 5 s_d_category - 2 View Definition: s_v_d_successful_touchpoint_executions: SELECT DISTINCT s_f_touchpoint_execution_status_history.touchpoint_execution_id FROM s_f_touchpoint_execution_status_history JOIN ( SELECT s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, max(s_f_touchpoint_execution_status_history_1.creation_dt) AS last_status_change FROM s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1 GROUP BY s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) tmp ON s_f_touchpoint_execution_status_history.touchpoint_execution_id = tmp.touchpoint_execution_id AND s_f_touchpoint_execution_status_history.creation_dt = tmp.last_status_change AND (s_f_touchpoint_execution_status_history.touchpoint_execution_status_type_id = ANY (ARRAY[3, 4, 6])); Table Used: s_f_touchpoint_execution_status_history Count: 291104 ========================================================================================================================= Table definitions: S_F_PROMOTION_HISTORY TABLE "PUBLIC.S_F_PROMOTION_HISTORY" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION ------------------------------+-----------------------------+--------------------+---------+--------------+------------- PROMO_HIST_ID | BIGINT | NOT NULL | PLAIN | | TARGET_ID | BIGINT | NOT NULL | PLAIN | | AUDIENCE_MEMBER_ID | BIGINT | NOT NULL | PLAIN | | TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | | CONTACT_GROUP_ID | BIGINT | NOT NULL | PLAIN | | CONTENT_VERSION_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | | SENT_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | SEND_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_F_PROMOTION_HISTORY_IDX_PR" BTREE (PROMO_HIST_ID) HAS OIDS: NO S_F_PROMOTION_HISTORY_EMAIL TABLE "PUBLIC.S_F_PROMOTION_HISTORY_EMAIL" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -------------------------+-----------------------------+--------------------+---------+--------------+------------- PROMO_HIST_ID | BIGINT | NOT NULL | PLAIN | | TARGET_ID | BIGINT | NOT NULL | PLAIN | | AUDIENCE_MEMBER_ID | BIGINT | NOT NULL | PLAIN | | TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | | CONTACT_GROUP_ID | BIGINT | NOT NULL | PLAIN | | SBOUNCE_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | HBOUNCE_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | OPENED_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | CLICKED_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | UNSUBSCRIBE_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | UNSUBSCRIBE_SPAM_IND | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | GROSS_OPEN_CNT | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | DISTINCT_CLICK_CNT | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | GROSS_CLICK_CNT | SMALLINT | NOT NULL DEFAULT 0 | PLAIN | | FIRST_CLICK_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | | FIRST_OPEN_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | | UNSUBSCRIBE_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | | UNSUBSCRIBE_SPAM_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | | BOUNCE_DT | TIMESTAMP WITHOUT TIME ZONE | | PLAIN | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_F_PROMOTION_HISTORY_EMAIL_PK1" PRIMARY KEY, BTREE (PROMO_HIST_ID) HAS OIDS: NO S_F_TOUCHPOINT_EXECUTION_STATUS_HISTORY TABLE "PUBLIC.S_F_TOUCHPOINT_EXECUTION_STATUS_HISTORY" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -------------------------------------+-----------------------------+----------------------------------------+----------+--------------+------------- TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | | TOUCHPOINT_EXECUTION_STATUS_TYPE_ID | INTEGER | NOT NULL | PLAIN | | STATUS_MESSAGE | CHARACTER VARYING(255) | NOT NULL DEFAULT ''::CHARACTER VARYING | EXTENDED | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | HAS OIDS: NO S_D_TOUCHPOINT_EXECUTION TABLE "PUBLIC.S_D_TOUCHPOINT_EXECUTION" COLUMN | TYPE | MODIFIERS -------------------------+-----------------------------+----------- TOUCHPOINT_EXECUTION_ID | BIGINT | NOT NULL WAVE_EXECUTION_ID | BIGINT | NOT NULL TOUCHPOINT_ID | BIGINT | NOT NULL CHANNEL_TYPE_ID | SMALLINT | NOT NULL CONTENT_ID | BIGINT | NOT NULL MESSAGE_TYPE_ID | SMALLINT | NOT NULL START_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL INDEXES: "S_D_TOUCHPOINT_EXECUTION_PKEY" PRIMARY KEY, BTREE (TOUCHPOINT_EXECUTION_ID, CHANNEL_TYPE_ID) S_D_WAVE_EXECUTION TABLE "PUBLIC.S_D_WAVE_EXECUTION" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -----------------------+-----------------------------+-----------+----------+--------------+------------- WAVE_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | | WAVE_ID | BIGINT | NOT NULL | PLAIN | | CAMPAIGN_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | | WAVE_EXECUTION_NAME | CHARACTER VARYING(300) | NOT NULL | EXTENDED | | START_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "WAVE_EXECUTION_ID_IDX" UNIQUE, BTREE (WAVE_EXECUTION_ID) HAS OIDS: NO S_D_CAMPAIGN_EXECUTION TABLE "PUBLIC.S_D_CAMPAIGN_EXECUTION" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -------------------------+-----------------------------+-----------+----------+--------------+------------- CAMPAIGN_EXECUTION_ID | BIGINT | NOT NULL | PLAIN | | CAMPAIGN_ID | BIGINT | NOT NULL | PLAIN | | GROUP_ID | SMALLINT | NOT NULL | PLAIN | | CAMPAIGN_EXECUTION_NAME | CHARACTER VARYING(300) | NOT NULL | EXTENDED | | START_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_CAMPAIGN_EXECUTION_IDX" BTREE (CAMPAIGN_EXECUTION_ID) HAS OIDS: NO S_D_TOUCHPOINT TABLE "PUBLIC.S_D_TOUCHPOINT" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -----------------+-----------------------------+-----------+----------+--------------+------------- TOUCHPOINT_ID | BIGINT | NOT NULL | PLAIN | | WAVE_ID | BIGINT | NOT NULL | PLAIN | | CHANNEL_TYPE_ID | SMALLINT | NOT NULL | PLAIN | | TOUCHPOINT_NAME | CHARACTER VARYING(255) | NOT NULL | EXTENDED | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_TOUCHPOINT_PKEY" PRIMARY KEY, BTREE (TOUCHPOINT_ID, CHANNEL_TYPE_ID) HAS OIDS: NO S_D_WAVE TABLE "PUBLIC.S_D_WAVE" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -------------+-----------------------------+-----------+----------+--------------+------------- WAVE_ID | BIGINT | NOT NULL | PLAIN | | CAMPAIGN_ID | BIGINT | NOT NULL | PLAIN | | WAVE_NAME | CHARACTER VARYING(255) | NOT NULL | EXTENDED | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_WAVE_PKEY" PRIMARY KEY, BTREE (WAVE_ID) HAS OIDS: NO S_D_CAMPAIGN TABLE "PUBLIC.S_D_CAMPAIGN" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION ---------------+-----------------------------+-----------+----------+--------------+------------- CAMPAIGN_ID | BIGINT | NOT NULL | PLAIN | | CAMPAIGN_NAME | CHARACTER VARYING(150) | NOT NULL | EXTENDED | | OBJECTIVE | CHARACTER VARYING(256) | | EXTENDED | | INITIATIVE | CHARACTER VARYING(256) | | EXTENDED | | CATEGORY_ID | SMALLINT | NOT NULL | PLAIN | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_CAMPAIGN_PKEY" PRIMARY KEY, BTREE (CAMPAIGN_ID) HAS OIDS: NO S_D_CONTENT TABLE "PUBLIC.S_D_CONTENT" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -----------------+-----------------------------+-----------+----------+--------------+------------- CONTENT_ID | BIGINT | NOT NULL | PLAIN | | CONTENT_NAME | CHARACTER VARYING(255) | NOT NULL | EXTENDED | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | CHANNEL_TYPE_ID | SMALLINT | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_CONTENT_PKEY" PRIMARY KEY, BTREE (CONTENT_ID, CHANNEL_TYPE_ID) HAS OIDS: NO S_D_MESSAGE_TYPE TABLE "PUBLIC.S_D_MESSAGE_TYPE" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -------------------+-----------------------------+-----------+----------+--------------+------------- MESSAGE_TYPE_ID | SMALLINT | NOT NULL | PLAIN | | MESSAGE_TYPE_NAME | CHARACTER VARYING(50) | NOT NULL | EXTENDED | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_MESSAGE_TYPE_PKEY" PRIMARY KEY, BTREE (MESSAGE_TYPE_ID) HAS OIDS: NO S_D_GROUP TABLE "PUBLIC.S_D_GROUP" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -------------+-----------------------------+-----------+----------+--------------+------------- GROUP_ID | SMALLINT | NOT NULL | PLAIN | | GROUP_NAME | CHARACTER VARYING(150) | NOT NULL | EXTENDED | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_GROUP_PKEY" PRIMARY KEY, BTREE (GROUP_ID) HAS OIDS: NO S_D_CHANNEL_TYPE COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION -------------------+-----------------------+-----------+----------+--------------+------------- CHANNEL_TYPE_ID | SMALLINT | NOT NULL | PLAIN | | CHANNEL_TYPE_NAME | CHARACTER VARYING(50) | NOT NULL | EXTENDED | | INDEXES: "D_CHANNEL_PK" PRIMARY KEY, BTREE (CHANNEL_TYPE_ID) HAS OIDS: NO S_D_CATEGORY TABLE "PUBLIC.S_D_CATEGORY" COLUMN | TYPE | MODIFIERS | STORAGE | STATS TARGET | DESCRIPTION ---------------+-----------------------------+-----------+----------+--------------+------------- CATEGORY_ID | SMALLINT | NOT NULL | PLAIN | | CATEGORY_NAME | CHARACTER VARYING(50) | NOT NULL | EXTENDED | | CREATION_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | MODIFIED_DT | TIMESTAMP WITHOUT TIME ZONE | NOT NULL | PLAIN | | INDEXES: "S_D_CATEGORY_PKEY" PRIMARY KEY, BTREE (CATEGORY_ID) HAS OIDS: NO ========================================================================================================================= Explain for the Query: "Merge Left Join (cost=711668.51..711668.64 rows=10 width=76)" " Merge Cond: (z.send_rank = (CASE WHEN (a.sent_ind <= 1) THEN 1 WHEN (a.sent_ind <= 5) THEN 2 WHEN (a.sent_ind <= 10) THEN 3 WHEN (a.sent_ind <= 15) THEN 4 WHEN (a.sent_ind <= 20) THEN 5 WHEN (a.sent_ind <= 30) THEN 6 WHEN (a.sent_ind <= 50) THEN 7 WHEN ( (...)" " -> Sort (cost=0.62..0.64 rows=10 width=36)" " Sort Key: z.send_rank" " -> Subquery Scan on z (cost=0.25..0.45 rows=10 width=36)" " -> HashAggregate (cost=0.25..0.35 rows=10 width=0)" " -> Append (cost=0.00..0.20 rows=10 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Result (cost=0.00..0.01 rows=1 width=0)" " -> Materialize (cost=711667.89..711667.96 rows=1 width=44)" " -> GroupAggregate (cost=711667.89..711667.95 rows=1 width=16)" " InitPlan 3 (returns $14)" " -> Aggregate (cost=355832.51..355832.52 rows=1 width=8)" " -> Nested Loop (cost=68171.53..355832.51 rows=1 width=8)" " Join Filter: (base_1.touchpoint_execution_id = tp_exec_1.touchpoint_execution_id)" " -> Nested Loop (cost=33666.96..37971.39 rows=1 width=894)" " Join Filter: (tp_exec_1.touchpoint_execution_id = valid_executions_2.touchpoint_execution_id)" " CTE valid_executions" " -> Hash Join (cost=13753.53..31711.17 rows=1 width=8)" " Hash Cond: ((s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history_2.touchpoint_execution_id) AND ((max(s_f_touchpoint_execution_status_history_1_1.creation_d (...)" " -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)" " -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_1 (cost=0.00..4766.04 rows=291104 width=16)" " -> Hash (cost=5493.80..5493.80 rows=135878 width=16)" " -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_2 (cost=0.00..5493.80 rows=135878 width=16)" " Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))" " -> Nested Loop Left Join (cost=1955.80..6260.19 rows=1 width=894)" " -> Nested Loop (cost=1955.67..6260.04 rows=1 width=776)" " -> Nested Loop Left Join (cost=1955.54..6259.87 rows=1 width=658)" " -> Nested Loop Left Join (cost=1955.40..6259.71 rows=1 width=340)" " -> Nested Loop Left Join (cost=1955.27..6259.55 rows=1 width=222)" " -> Nested Loop (cost=1954.99..6259.24 rows=1 width=197)" " -> Nested Loop (cost=1954.71..6258.92 rows=1 width=173)" " Join Filter: (camp_exec_1.campaign_id = wave_1.campaign_id)" " -> Nested Loop (cost=1954.42..6254.67 rows=13 width=167)" " -> Hash Join (cost=1954.13..6249.67 rows=13 width=108)" " Hash Cond: ((tp_exec_1.touchpoint_id = tp_2.touchpoint_id) AND (wave_exec_1.wave_id = tp_2.wave_id))" " -> Hash Join (cost=1576.83..4595.51 rows=72956 width=90)" " Hash Cond: (tp_exec_1.wave_execution_id = wave_exec_1.wave_execution_id)" " -> Seq Scan on s_d_touchpoint_execution tp_exec_1 (cost=0.00..1559.56 rows=72956 width=42)" " -> Hash (cost=1001.37..1001.37 rows=46037 width=56)" " -> Seq Scan on s_d_wave_execution wave_exec_1 (cost=0.00..1001.37 rows=46037 width=56)" " -> Hash (cost=212.72..212.72 rows=10972 width=26)" " -> Seq Scan on s_d_touchpoint tp_2 (cost=0.00..212.72 rows=10972 width=26)" " -> Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec_1 (cost=0.29..0.37 rows=1 width=67)" " Index Cond: (campaign_execution_id = wave_exec_1.campaign_execution_id)" " -> Index Scan using s_d_wave_pkey on s_d_wave wave_1 (cost=0.29..0.31 rows=1 width=22)" " Index Cond: (wave_id = wave_exec_1.wave_id)" " -> Index Scan using s_d_campaign_pkey on s_d_campaign camp_1 (cost=0.29..0.32 rows=1 width=40)" " Index Cond: (campaign_id = camp_exec_1.campaign_id)" " -> Index Scan using s_d_content_pkey on s_d_content content_1 (cost=0.28..0.30 rows=1 width=33)" " Index Cond: (tp_exec_1.content_id = content_id)" " -> Index Scan using s_d_message_type_pkey on s_d_message_type message_type_1 (cost=0.13..0.15 rows=1 width=120)" " Index Cond: (tp_exec_1.message_type_id = message_type_id)" " -> Index Scan using s_d_group_pkey on s_d_group grup_1 (cost=0.13..0.15 rows=1 width=320)" " Index Cond: (camp_exec_1.group_id = group_id)" " -> Index Scan using d_channel_pk on s_d_channel_type channel_1 (cost=0.13..0.15 rows=1 width=120)" " Index Cond: (channel_type_id = tp_2.channel_type_id)" " -> Index Scan using s_d_category_pkey on s_d_category "CATEGORY_1" (cost=0.13..0.15 rows=1 width=120)" " Index Cond: (camp_1.category_id = category_id)" " -> CTE Scan on valid_executions valid_executions_2 (cost=0.00..0.02 rows=1 width=8)" " -> Nested Loop Left Join (cost=34504.57..317861.06 rows=2 width=148)" " CTE valid_executions" " -> Nested Loop (cost=32782.31..34504.28 rows=1 width=8)" " -> Nested Loop (cost=32782.03..34503.96 rows=1 width=16)" " -> Unique (cost=32781.61..34495.50 rows=1 width=8)" " -> Merge Join (cost=32781.61..34495.50 rows=1 width=8)" " Merge Cond: ((s_f_touchpoint_execution_status_history.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) AND (s_f_touchpoint_execution_status_history.crea (...)" " -> Sort (cost=19697.87..20098.14 rows=160107 width=16)" " Sort Key: s_f_touchpoint_execution_status_history.touchpoint_execution_id, s_f_touchpoint_execution_status_history.creation_dt" " -> Seq Scan on s_f_touchpoint_execution_status_history (cost=0.00..5857.68 rows=160107 width=16)" " Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))" " -> Sort (cost=13083.74..13254.76 rows=68410 width=16)" " Sort Key: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1.creation_dt))" " -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)" " -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1 (cost=0.00..4766.04 rows=291104 width=16)" " -> Index Scan using s_d_touchpoint_execution_pkey on s_d_touchpoint_execution tpe (cost=0.42..8.44 rows=1 width=16)" " Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id)" " -> Index Only Scan using s_d_touchpoint_pkey on s_d_touchpoint tp_1 (cost=0.29..0.32 rows=1 width=8)" " Index Cond: ((touchpoint_id = tpe.touchpoint_id) AND (channel_type_id = 1))" " -> Nested Loop (cost=0.00..283351.20 rows=2 width=74)" " Join Filter: (base_1.touchpoint_execution_id = valid_executions_3.touchpoint_execution_id)" " -> CTE Scan on valid_executions valid_executions_3 (cost=0.00..0.02 rows=1 width=8)" " -> Seq Scan on s_f_promotion_history base_1 (cost=0.00..283334.17 rows=1361 width=74)" " Filter: ((send_dt >= '2014-03-13 00:00:00'::timestamp without time zone) AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time zone))" " -> Index Scan using s_f_promotion_history_email_pk1 on s_f_promotion_history_email email_1 (cost=0.29..2.76 rows=1 width=90)" " Index Cond: (base_1.promo_hist_id = promo_hist_id)" " Filter: (base_1.audience_member_id = audience_member_id)" " -> Sort (cost=355835.37..355835.38 rows=1 width=16)" " Sort Key: (CASE WHEN (a.sent_ind <= 1) THEN 1 WHEN (a.sent_ind <= 5) THEN 2 WHEN (a.sent_ind <= 10) THEN 3 WHEN (a.sent_ind <= 15) THEN 4 WHEN (a.sent_ind <= 20) THEN 5 WHEN (a.sent_ind <= 30) THEN 6 WHEN (a.sent_ind <= 50) THEN 7 WHEN (...)" " -> Subquery Scan on a (cost=355835.32..355835.36 rows=1 width=16)" " -> HashAggregate (cost=355835.32..355835.33 rows=1 width=16)" " -> Nested Loop (cost=68171.53..355835.31 rows=1 width=16)" " Join Filter: (base.touchpoint_execution_id = tp_exec.touchpoint_execution_id)" " -> Nested Loop (cost=33666.96..37971.39 rows=1 width=894)" " Join Filter: (tp_exec.touchpoint_execution_id = valid_executions.touchpoint_execution_id)" " CTE valid_executions" " -> Hash Join (cost=13753.53..31711.17 rows=1 width=8)" " Hash Cond: ((s_f_touchpoint_execution_status_history_1_3.touchpoint_execution_id = s_f_touchpoint_execution_status_history_4.touchpoint_execution_id) AND ((max(s_f_touchpoint_execution_status_history_1_3. (...)" " -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)" " -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_3 (cost=0.00..4766.04 rows=291104 width=16)" " -> Hash (cost=5493.80..5493.80 rows=135878 width=16)" " -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_4 (cost=0.00..5493.80 rows=135878 width=16)" " Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))" " -> Nested Loop Left Join (cost=1955.80..6260.19 rows=1 width=894)" " -> Nested Loop (cost=1955.67..6260.04 rows=1 width=776)" " -> Nested Loop Left Join (cost=1955.54..6259.87 rows=1 width=658)" " -> Nested Loop Left Join (cost=1955.40..6259.71 rows=1 width=340)" " -> Nested Loop Left Join (cost=1955.27..6259.55 rows=1 width=222)" " -> Nested Loop (cost=1954.99..6259.24 rows=1 width=197)" " -> Nested Loop (cost=1954.71..6258.92 rows=1 width=173)" " Join Filter: (camp_exec.campaign_id = wave.campaign_id)" " -> Nested Loop (cost=1954.42..6254.67 rows=13 width=167)" " -> Hash Join (cost=1954.13..6249.67 rows=13 width=108)" " Hash Cond: ((tp_exec.touchpoint_id = tp.touchpoint_id) AND (wave_exec.wave_id = tp.wave_id))" " -> Hash Join (cost=1576.83..4595.51 rows=72956 width=90)" " Hash Cond: (tp_exec.wave_execution_id = wave_exec.wave_execution_id)" " -> Seq Scan on s_d_touchpoint_execution tp_exec (cost=0.00..1559.56 rows=72956 width=42)" " -> Hash (cost=1001.37..1001.37 rows=46037 width=56)" " -> Seq Scan on s_d_wave_execution wave_exec (cost=0.00..1001.37 rows=46037 width=56)" " -> Hash (cost=212.72..212.72 rows=10972 width=26)" " -> Seq Scan on s_d_touchpoint tp (cost=0.00..212.72 rows=10972 width=26)" " -> Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec (cost=0.29..0.37 rows=1 width=67)" " Index Cond: (campaign_execution_id = wave_exec.campaign_execution_id)" " -> Index Scan using s_d_wave_pkey on s_d_wave wave (cost=0.29..0.31 rows=1 width=22)" " Index Cond: (wave_id = wave_exec.wave_id)" " -> Index Scan using s_d_campaign_pkey on s_d_campaign camp (cost=0.29..0.32 rows=1 width=40)" " Index Cond: (campaign_id = camp_exec.campaign_id)" " -> Index Scan using s_d_content_pkey on s_d_content content (cost=0.28..0.30 rows=1 width=33)" " Index Cond: (tp_exec.content_id = content_id)" " -> Index Scan using s_d_message_type_pkey on s_d_message_type message_type (cost=0.13..0.15 rows=1 width=120)" " Index Cond: (tp_exec.message_type_id = message_type_id)" " -> Index Scan using s_d_group_pkey on s_d_group grup (cost=0.13..0.15 rows=1 width=320)" " Index Cond: (camp_exec.group_id = group_id)" " -> Index Scan using d_channel_pk on s_d_channel_type channel (cost=0.13..0.15 rows=1 width=120)" " Index Cond: (channel_type_id = tp.channel_type_id)" " -> Index Scan using s_d_category_pkey on s_d_category "CATEGORY" (cost=0.13..0.15 rows=1 width=120)" " Index Cond: (camp.category_id = category_id)" " -> CTE Scan on valid_executions (cost=0.00..0.02 rows=1 width=8)" " -> Nested Loop Left Join (cost=34504.57..317863.87 rows=2 width=148)" " CTE valid_executions" " -> Nested Loop (cost=32782.31..34504.28 rows=1 width=8)" " -> Nested Loop (cost=32782.03..34503.96 rows=1 width=16)" " -> Unique (cost=32781.61..34495.50 rows=1 width=8)" " -> Merge Join (cost=32781.61..34495.50 rows=1 width=8)" " Merge Cond: ((s_f_touchpoint_execution_status_history_3.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1_2.touchpoint_execution_id) AND (s_f_touchpoint_execution_statu (...)" " -> Sort (cost=19697.87..20098.14 rows=160107 width=16)" " Sort Key: s_f_touchpoint_execution_status_history_3.touchpoint_execution_id, s_f_touchpoint_execution_status_history_3.creation_dt" " -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_3 (cost=0.00..5857.68 rows=160107 width=16)" " Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))" " -> Sort (cost=13083.74..13254.76 rows=68410 width=16)" " Sort Key: s_f_touchpoint_execution_status_history_1_2.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1_2.creation_dt))" " -> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16)" " -> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_2 (cost=0.00..4766.04 rows=291104 width=16)" " -> Index Scan using s_d_touchpoint_execution_pkey on s_d_touchpoint_execution tpe_1 (cost=0.42..8.44 rows=1 width=16)" " Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history_3.touchpoint_execution_id)" " -> Index Only Scan using s_d_touchpoint_pkey on s_d_touchpoint tp_3 (cost=0.29..0.32 rows=1 width=8)" " Index Cond: ((touchpoint_id = tpe_1.touchpoint_id) AND (channel_type_id = 1))" " -> Nested Loop (cost=0.00..283354.75 rows=2 width=74)" " Join Filter: (base.touchpoint_execution_id = valid_executions_1.touchpoint_execution_id)" " -> CTE Scan on valid_executions valid_executions_1 (cost=0.00..0.02 rows=1 width=8)" " -> Seq Scan on s_f_promotion_history base (cost=0.00..283334.17 rows=1645 width=74)" " Filter: ((send_dt >= '2014-01-01 00:00:00'::timestamp without time zone) AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time zone))" " -> Index Scan using s_f_promotion_history_email_pk1 on s_f_promotion_history_email email (cost=0.29..2.39 rows=1 width=90)" " Index Cond: (base.promo_hist_id = promo_hist_id)" " Filter: (base.audience_member_id = audience_member_id)" ========================================================================================================================= EXPLAIN ANALYZE: Query Never completed Postgres version : PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-54), 64-bit History : Running for the first time. Maintenance Setup: New Server and data has been just inserted. WAL Configuration: Default Settings shared_buffers = 6GB work_mem = 1GB maintenance_work_mem = 3GB effective_cache_size = 16GB All are commented: #enable_bitmapscan = on #enable_hashagg = on #enable_hashjoin = on #enable_indexscan = on #enable_indexonlyscan = on #enable_material = on #enable_mergejoin = on #enable_nestloop = on #enable_seqscan = on #enable_sort = on #enable_tidscan = on vmstat procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 548472 8193880 46160 5597832 0 0 8 34 0 0 1 0 99 0 0 ============== iostat Linux 2.6.18-348.4.1.el5 (AT-PU-MERCURY) 03/13/2015 avg-cpu: %user %nice %system %iowait %steal %idle 0.54 0.00 0.15 0.04 0.00 99.26 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 5.72 62.89 262.98 3661856221 15313306008 sda1 0.00 0.02 0.00 911167 35000 sda2 5.72 62.87 262.98 3660943046 15313271008 dm-0 7.43 4.66 57.98 271216554 3375965736 dm-1 24.10 57.78 188.70 3364343202 10988093896 dm-2 1.72 0.00 13.79 127826 803126920 dm-3 0.37 0.43 2.51 25255000 146084456 ============== df -k Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/VolGroup00-LogVol00 49580256 36870396 10150680 79% / /dev/mapper/VolGroup00-LogVol03 748313168 660142564 49545324 94% /var /dev/mapper/VolGroup00-LogVol02 19838052 211072 18602980 2% /tmp /dev/sda1 295561 24745 255556 9% /boot tmpfs 12333076 0 12333076 0% /dev/shm ======== cat /proc/meminfo MemTotal: 24666152 kB MemFree: 8192640 kB Buffers: 46512 kB Cached: 5597864 kB SwapCached: 183964 kB Active: 15507332 kB Inactive: 756136 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 24666152 kB LowFree: 8192640 kB SwapTotal: 32767992 kB SwapFree: 32219520 kB Dirty: 292 kB Writeback: 0 kB AnonPages: 10617088 kB Mapped: 2175692 kB Slab: 120216 kB PageTables: 38128 kB NFS_Unstable: 0 kB Bounce: 0 kB CommitLimit: 45101068 kB Committed_AS: 18324080 kB VmallocTotal: 34359738367 kB VmallocUsed: 279376 kB VmallocChunk: 34359458551 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 Hugepagesize: 2048 kB =========================